package com.baturu.vin.honda.dal.dao;

import com.baturu.vin.base.dal.dto.TestSourceDataDTO;
import com.baturu.vin.honda.dal.entity.Vin;
import com.baturu.vin.honda.dto.HondaPartsInfoDTO;
import com.baturu.vin.honda.dto.HondaPartsMappingDTO;
import com.baturu.vin.honda.dto.PartsmappingDTO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Collection;
import java.util.List;

/**
 * @Author: chenjiahao
 * @Time: 2019/10/15
 */
public interface SimpleDAO {

    @Select({"SELECT partsCode FROM honda_parts_mapping WHERE BTRPARTSNAME = #{name} limit 1"})
    HondaPartsMappingDTO queryPartsName(@Param("name") String partsName);

    @Select({"SELECT DISTINCT nplblk FROM `honda_parts_info`",
            "where npartgenu = #{code} limit 1"})
    HondaPartsInfoDTO queryImageCode(@Param("code") String partsCode);

    @Select({"SELECT DISTINCT btrPartsName FROM honda_parts_info t",
            "INNER JOIN honda_parts_mapping tt on t.npartgenu = tt.partsCode",
            "WHERE t.nplblk = #{dto.nplblk} limit 30"})
    List<HondaPartsMappingDTO> queryBtrPartsName(@Param("dto") HondaPartsInfoDTO partsInfoDTO);

    @Select({"SELECT vin_code vinCode,parts_name btrPartsName,btrid FROM tmp_1022_2 WHERE id >=${s} and id < ${e}"})
    List<TestSourceDataDTO> queryData(@Param("s") int s, @Param("e") int e);

    @Select("SELECT max(id) FROM honda_parts_cartype")
    int queryMAXId();


    @Select("SELECT max(id) FROM honda_parts_cartype")
    int queryCartypeMAXId();

    @Insert({"insert ignore into honda_parts_cartype (brand,parts_code,car_system_id,epc_carType_code,mj,btrid) \n" +
            "select DISTINCT t.BTRBrand,ttt.npartgenu,t.carSystemId,t.Vcode,t.Mj,t.btrid from honda_cartype t \n" +
            "INNER JOIN honda_vin_cartype_20191206 tt on tt.btrid=t.btrid and tt.vcode =t.vcode \n" +
            "INNER JOIN vin_mj t1 on t1.mj = t.mj \n" +
            "INNER JOIN honda_parts_info ttt on ttt.npl = tt.npl and t.disk = ttt.disk \n" +
            "left JOIN honda_pbpmtt t2 on  t2.HPARTPLBLK = ttt.hpartplblk and t2.disk = ttt.disk \n" +
            "INNER JOIN honda_pblmtt t4 on t2.id is null and t4.disk = ttt.disk and t4.NPL=ttt.NPL and t4.NPLBLK = ttt.nplblk and t4.HMODTYP = t.hmodtyp  \n" +
            "where \n" +
            "t1.v> ttt.NSEREPCSTRT and t1.v<ttt.NSEREPCEND  and ttt.disk in ( 13,14)  \n" +
            "and t.id >=${s} and t.id <${e}" ,
            "union select DISTINCT t.BTRBrand,ttt.npartgenu,t.carSystemId,t.Vcode,t.Mj,t.btrid from honda_cartype t\n" +
            "INNER JOIN honda_vin_cartype_20191206 tt on tt.btrid=t.btrid and tt.vcode =t.vcode\n" +
            "INNER JOIN vin_mj t1 on t1.mj = t.mj\n" +
            "INNER JOIN honda_parts_info ttt on ttt.npl = tt.npl\n" +
            "inner JOIN honda_pbpmtt t2 on  t2.HPARTPLBLK = ttt.hpartplblk and t2.disk = ttt.disk and t2.HMODTYP = t.HMODTYP\n" +
            "where \n" +
            "t1.v> ttt.NSEREPCSTRT and t1.v<ttt.NSEREPCEND  and ttt.disk in ( 13,14)  ",
            "and t.id >=${s} and t.id <${e}"
    })
    void inster(@Param("s") int s, @Param("e") int e);

    @Insert({"insert ignore into tmp_parts_epc_0416(epcid,partscode,npl,nplblk,prePartsCode)\n" +
            "select DISTINCT t.btrid,t.parts_code,t1.npl,t2.nplblk,substr(t.parts_code,1,5) from honda_parts_cartype t\n" +
            "INNER JOIN honda_vin_info t1 on t1.btrId = t.btrid\n" +
            "INNER JOIN honda_parts_info t2 on t2.npl = t1.npl and t.parts_code = t2.npartgenu",
            "WHERE t.id >=${s} and t.id <${e}"
    })
    void insters(@Param("s") int s, @Param("e") int e);


    @Select({"SELECT max(id) FROM tmp_1216_3"})
    int querytmp_1216_3MAXId();

    @Select({"select * from tmp_1216_3 where id>=${s} and id< ${e}"})
    List<Vin> querytmp_1216_3(@Param("s") int s, @Param("e") int e);

    @Insert({"${sql}"})
    void insert(@Param("sql") String sql);

    //930000
//    @Select({"SELECT max(id) FROM tmp_vin_partsname_0416"})
    @Select({"SELECT max(id) FROM tmp_vin_partsname_0421"})
    int queryMaxId();

    @Select({"select * from tmp_vin_partsname_0421 where id <= ${end} and id > ${start}"})
    List<PartsmappingDTO> get(@Param("start") int start, @Param("end") int end);

    @Insert({"<script>",

            "INSERT Ignore INTO tmp_parts_btrid_detail_vin_0416 (epcId,btrid,partscode,partsname,npl, nplblk, prePartsCode) values",
            "<foreach item = 'dto' index='index' collection = 'list' open = '(' separator='),(' close = ')'>",
            "#{dto.epcId},#{dto.btrid},#{dto.partsCode},#{dto.partsname},#{dto.npl}, #{dto.nplblk}, #{dto.prePartsCode}",
            "</foreach>",
            "</script>"
    })
    int insertdata(@Param("list") List<PartsmappingDTO> data);

    @Select({"select id from tmp_del_id_0421 where id <= ${end} and id > ${start}"})
    List<Integer> getDel(@Param("start") int start, @Param("end") int end);

    @Update({"<script>DELETE from tmp_vin_partsname_0421 where id in ",
            "<foreach item = 'dto' index='index' collection = 'list' open = '(' separator=',' close = ')'>",
            "#{dto}",
            "</foreach></script>",})
    void del(@Param("list") Collection<Integer> ints);




}
